#!/usr/bin/env python
# -*- coding: UTF-8 -*-
"""
@Project ：tools 
@File ：成品合同匹配（无验证码版本.py
@IDE  ：PyCharm 
@Author ：李涵彬
@Date ：2024/11/27 上午10:52 
"""

"""
需要操作合同表，物料表，减宽量配置表，以优充次配置表

1.读取四个excel表格，表格可能是csv、xls、xlsx格式文件
2.逐行遍历物料表，使用“出钢标记”在合同表中进行数据匹配，如果能够匹配到数据，则进行3、4，如果无法匹配到数据，则跳过3、4，从5开始执行
3.如果物料表中“推荐轧宽”列存在值，则解析该单元格的两个数值，分别作为范围最小值和范围最大值；使用该范围与合同表中的“轧宽”进行匹配，如果该物料轧宽在范围内，则该合同号符合要求，添加这个合同号
4.如果物料表中“推荐轧宽”列不存在值，则通过减宽量配置表解析计算“推荐轧宽”范围，需要首先获取合同表中的“全程途径码”的第9、第10位，在减宽量配置表中匹配“产线”列，“宽度小值”作为范围最小值，“宽度大值”作为范围最大值，之后使用该范围与合同表中的“轧宽”+对应“减宽量”进行匹配，如果在范围内，则添加这个合同号
5.如果使用“出钢标记”在合同表中进行数据匹配，不能够匹配到数据，则在以优充次配置表中，匹配“(材料)出钢标记”列，将对应的“(合同)出钢标记”作为“出钢标记”进行二次匹配，如果“钢级代码”列存在值，则使用“出钢标记”和“钢级代码”同时进行匹配，如果不存在值，则解析“宽度上限”“宽度下限”“厚度上限”“厚度下限”，使用这两个范围，分别对“轧宽”“轧厚”进行范围判定，符合范围内的，进行合同号添加
6.构建dataframe格式的result变量，第一列为材料号，第二列为出钢标记，之后，匹配到的合同号依次填充
7.最后将result保存到桌面
"""

import tkinter as tk
from tkinter import filedialog, messagebox, ttk
import pandas as pd
import ast
import logging
from pathlib import Path
from typing import List

# 日志配置
LOG_DIR = Path(__file__).parent / 'logs'
LOG_DIR.mkdir(exist_ok=True)
LOG_FILE = LOG_DIR / 'app.log'

logging.basicConfig(
	level=logging.INFO,
	format='%(asctime)s - %(levelname)s - %(message)s',
	handlers=[logging.FileHandler(LOG_FILE), logging.StreamHandler()]
)

# 设置常量路径
REDUCTION_WIDTH_CONFIG_PATH = './config/热轧产线减宽量配置表.xlsx'
SUBSTITUTE_CONFIG_PATH = './config/以优充次配置表.xlsx'


def read_excel_file(file_path: str) -> pd.DataFrame:
	"""读取 Excel 文件，并处理可能的异常"""
	try:
		return pd.read_excel(file_path)
	except Exception as e:
		logging.error(f"无法读取文件 {file_path}: {e}")
		raise


def match_contracts_by_width(matched_contracts: pd.DataFrame, min_width: float, max_width: float) -> pd.DataFrame:
	"""通过轧宽范围匹配合同"""
	return matched_contracts[(matched_contracts['轧宽'] >= min_width) & (matched_contracts['轧宽'] <= max_width)]


def match_contracts_by_reduction_width(material_row_width: int, matched_contracts: pd.DataFrame,
									   reduction_width_config_df: pd.DataFrame) -> pd.DataFrame:
	"""通过减宽量配置表计算推荐轧宽范围"""
	matched_contracts_res = pd.DataFrame(columns=matched_contracts.columns)
	for index, row in matched_contracts.iterrows():
		reduction_config = reduction_width_config_df[reduction_width_config_df['产线'] == row['全程途径码'][8:10]]
		if reduction_config.empty:
			logging.warning(f"未找到减宽量配置")
		else:
			for _, sub_row in reduction_config.iterrows():
				if sub_row['宽度小值'] <= row['轧宽'] <= sub_row['宽度大值']:
					reduction_width = sub_row['减宽量']
					min_width, max_width = row['轧宽'], row['轧宽'] + reduction_width
					if min_width <= material_row_width <= max_width:
						matched_contracts_res = pd.concat([matched_contracts_res, pd.DataFrame([row])],
														  ignore_index=True)
						break
	return matched_contracts_res


def substitute_contracts(material_row_width: int, material_drawing_code: str, substitute_config_df: pd.DataFrame,
						 reduction_width_config_df: pd.DataFrame, contracts_df: pd.DataFrame) -> List[str]:
	"""通过以优充次配置表匹配合同"""
	# 筛选出以优充次物料对应配置表
	substitute_match = substitute_config_df[substitute_config_df['(材料)出钢标记'] == material_drawing_code]
	if substitute_match.empty:
		return []

	matched_contracts_res = pd.DataFrame(columns=contracts_df.columns)

	# 遍历以优充次配置表
	for index, row in substitute_match.iterrows():
		logging.info(f"开始匹配以优充次配置：{row['(合同)出钢标记']}")

		substitute_drawing_code = row['(合同)出钢标记']
		substitute_sign_code = row['钢级代码']
		# 筛选出以优充次出钢标记和钢级代码对应的合同
		# 如果钢级代码字段非空，则使用出钢标记和钢级代码进行匹配
		if substitute_sign_code != '' and substitute_sign_code != ' ':
			substitute_contracts = contracts_df[
				(contracts_df['出钢标记'] == substitute_drawing_code) & (
						contracts_df['钢级代码'] == substitute_sign_code)]
		else:
			# 如果钢级代码字段为空，则使用出钢标记进行匹配
			substitute_contracts = contracts_df[contracts_df['出钢标记'] == substitute_drawing_code]

		if substitute_contracts.empty:
			logging.warning(f"未找到以优充次配置匹配的合同：{row['(合同)出钢标记']}")
			continue
		else:
			# 如果存在以优充次合同，则使用规格宽度、规格厚度进行匹配
			min_width, max_width, min_thickness, max_thickness = row[
				['宽度下限', '宽度上限', '厚度下限', '厚度上限']]
			substitute_contracts = substitute_contracts[
				(substitute_contracts['轧宽'] >= min_width) & (substitute_contracts['轧宽'] <= max_width) &
				(substitute_contracts['轧厚'] >= min_thickness) & (substitute_contracts['轧厚'] <= max_thickness)
				]

		substitute_contracts = match_contracts_by_reduction_width(material_row_width, substitute_contracts,
																  reduction_width_config_df)

		matched_contracts_res = pd.concat([matched_contracts_res, substitute_contracts], ignore_index=True)

	return matched_contracts_res['合同号'].dropna().unique().tolist()


def process_materials(materials_df: pd.DataFrame, contracts_df: pd.DataFrame, reduction_width_config_df: pd.DataFrame,
					  substitute_config_df: pd.DataFrame, progress_var: tk.DoubleVar) -> pd.DataFrame:
	"""处理所有物料行并匹配合同号"""
	result = []
	for index, material_row in materials_df.iterrows():
		material_drawing_code = material_row['出钢标记']
		material_number = material_row['材料号']
		material_recommend_width = material_row.get('推荐轧宽')

		logging.info(
			f"处理物料行：{index + 1}/{len(materials_df)}，材料号：{material_number}，出钢标记：{material_drawing_code}")

		matched_contracts = contracts_df[contracts_df['出钢标记'] == material_drawing_code]
		contract_numbers = []

		# 如果出钢标记筛选不为空则执行正常一次匹配
		if not matched_contracts.empty:
			if material_recommend_width != '' and material_recommend_width != ' ':
				# 如果存在推荐轧宽，则根据推荐轧宽匹配合同
				min_width, max_width = ast.literal_eval(material_recommend_width)
				matched_contracts = match_contracts_by_width(matched_contracts, min_width, max_width)
			else:
				# 如果不存在推荐轧宽，则根据减宽量配置表匹配合同
				matched_contracts = match_contracts_by_reduction_width(material_row['宽(mm)'], matched_contracts,
																	   reduction_width_config_df)
			contract_numbers = matched_contracts['合同号'].dropna().unique().tolist()
		else:
			# 如果出钢标记筛选为空，则执行以优充次匹配
			contract_numbers = substitute_contracts(material_row['宽(mm)'], material_drawing_code, substitute_config_df,
													reduction_width_config_df, contracts_df)

		if contract_numbers == []:
			# 如果未找到匹配合同，则跳过后续筛选
			logging.warning(f"未找到匹配合同：{material_number}")
			continue

		# res_contract_numbers = []
		# result.append({
		# 	'材料号': material_number,
		# 	'出钢标记': material_drawing_code,
		# 	'合同号': ','.join(res_contract_numbers) if res_contract_numbers else None
		# })
		#
		# # 更新进度条
		# progress_var.set(index / len(materials_df))
		# continue

		# 机组特殊指令筛选
		# 如果物料机组特殊指令中包含“4.0mm”，则合同中的“轧厚”需要大于4.0
		if '4.0mm' in str(material_row['机组特殊指令']):
			for contract_number in contract_numbers:
				tmp_match_contract = contracts_df[contracts_df['合同号'] == contract_number]
				if float(tmp_match_contract['轧厚']) < 4:
					contract_numbers.remove(contract_number)

		if contract_numbers == []:
			# 如果未找到匹配合同，则跳过后续筛选
			logging.warning(f"未找到匹配合同：{material_number}")
			continue

		# 吨重能否分配进行校验
		res_contract_numbers = []
		for contract_number in contract_numbers:
			contract_row = contracts_df[contracts_df['合同号'] == contract_number]
			min_weight = float(contract_row['订货重量单件最小值'])
			max_weight = float(contract_row['订货重量单件最大值'])
			for i in range(1, 4):
				sub_min_weight = i * min_weight
				sub_max_weight = i * max_weight
				if sub_min_weight <= material_row['重量(t)'] <= sub_max_weight:
					res_contract_numbers.append(contract_number)
					break

		if res_contract_numbers == []:
			# 如果未找到匹配合同，则跳过后续筛选
			logging.warning(f"未找到匹配合同：{material_number}")
			continue

		result.append({
			'材料号': material_number,
			'出钢标记': material_drawing_code,
			'合同号': ','.join(res_contract_numbers) if res_contract_numbers else None
		})

		# 更新进度条
		progress_var.set(index / len(materials_df))

	result_df = pd.DataFrame(result)

	split_contract_columns = result_df['合同号'].str.split(',', expand=True)
	max_columns = split_contract_columns.shape[1]
	for i in range(max_columns):
		result_df[f'合同号{i + 1}'] = split_contract_columns[i]

	result_df.drop('合同号', axis=1, inplace=True)

	return result_df


def save_result_to_excel(result_df: pd.DataFrame, file_path: str):
	"""保存结果到Excel文件"""
	result_df.to_excel(file_path, index=False)


def main(contract_path, material_path, reduction_width_config_path, substitute_config_path, progress_var):
	# 读取文件
	contracts_df = read_excel_file(contract_path)
	materials_df = read_excel_file(material_path)
	reduction_width_config_df = read_excel_file(reduction_width_config_path)
	substitute_config_df = read_excel_file(substitute_config_path)

	# 处理物料并匹配合同号
	result_df = process_materials(materials_df, contracts_df, reduction_width_config_df, substitute_config_df,
								  progress_var)

	# 保存到桌面
	desktop_path = Path.home() / 'Desktop'
	result_path = desktop_path / 'result.xlsx'
	save_result_to_excel(result_df, result_path)
	logging.info(f"结果已保存到桌面路径：{result_path}")
	messagebox.showinfo("完成", "处理完成，结果已保存到桌面。")


def browse_file():
	"""浏览文件对话框"""
	file_path = filedialog.askopenfilename(
		title="选择文件",
		filetypes=[("Excel files", "*.xlsx *.xls *.csv")]
	)
	return file_path


def start_execution(progress_var):
	"""开始执行按钮的回调函数"""
	# 获取文件路径
	contract_path = contract_entry.get()
	material_path = material_entry.get()

	# 检查文件路径是否已选择
	if not contract_path or not material_path:
		messagebox.showerror("错误", "请先选择所有文件")
		return

	# 调用原始脚本的main函数，传入文件路径
	main(contract_path, material_path, REDUCTION_WIDTH_CONFIG_PATH, SUBSTITUTE_CONFIG_PATH, progress_var)


# 创建主窗口
root = tk.Tk()
root.title("成品合同匹配工具")

# 设置窗口大小，大约和微信窗口一个大小
root.geometry("800x600")

# 设置字体大小
font_size = 14

# 创建标签和输入框
tk.Label(root, text="热轧合同明细路径:", font=("Arial", font_size)).pack(pady=5)
contract_entry = tk.Entry(root, font=("Arial", font_size), width=50)
contract_entry.pack(pady=5)

contract_button = tk.Button(root, text="浏览", command=lambda: contract_entry.insert(0, browse_file()),
							font=("Arial", font_size))
contract_button.pack(pady=5)

tk.Label(root, text="余材明细表路径:", font=("Arial", font_size)).pack(pady=5)
material_entry = tk.Entry(root, font=("Arial", font_size), width=50)
material_entry.pack(pady=5)

material_button = tk.Button(root, text="浏览", command=lambda: material_entry.insert(0, browse_file()),
							font=("Arial", font_size))
material_button.pack(pady=5)

# 创建进度条
progress_var = tk.DoubleVar()
progress_bar = ttk.Progressbar(root, length=200, variable=progress_var, maximum=1.0)
progress_bar.pack(pady=20)

# 创建开始执行按钮
start_button = tk.Button(root, text="开始执行", command=lambda: start_execution(progress_var),
						 font=("Arial", font_size))
start_button.pack(pady=20)

# 运行主循环
root.mainloop()
